package com.zhuiyun.educations.api.newCourse.mapper;

import com.zhuiyun.educations.api.newCourse.entity.CourseInfo;
import com.zhuiyun.educations.api.newCourse.entity.DTO.CourseDTO;
import com.zhuiyun.educations.common.mapper.BaseMapper;
import org.apache.ibatis.annotations.*;

import javax.lang.model.type.IntersectionType;
import java.util.List;

/**
 * @ClassName CourseInfoMapper
 * @Description TODO 课程信息持久层
 * @Author 杨友朋
 * @Date 2023/6/28 16:54
 **/
@Mapper
public interface CourseInfoMapper extends BaseMapper<CourseInfo> {

    /**
    * @Author 杨友朋
    * @Description TODO 添加课程信息
    * @Date 2023/6/29 10:08
    * @Return Integer
    * @param courseInfo
    */
    @Insert("insert into course_info (course_title,introduction,course_id,image_id,teacher_id,course_type,pay_type,type,create_time,update_time) " +
            "values (#{courseTitle},#{introduction},#{courseId},#{imageId},#{teacherId},#{courseType},#{payType},#{type},#{createTime},#{updateTime})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    Integer insertCourseInfo(CourseInfo courseInfo);

    /**
    * @Author 杨友朋
    * @Description TODO 根据id单查课程信息
    * @Date 2023/6/29 10:08
    * @Return CourseInfo
    * @param id
    */
    @Select("select * from course_info where id=#{id}")
    CourseInfo selectOneById(Integer id);

    /**
    * @Author 杨友朋
    * @Description TODO 根据id修改信息
    * @Date 2023/6/30 8:40
    * @Return Integer
    * @param courseInfo
    */
    @Update("<script>update course_info set " +
            "<if test='courseTitle!=null'>course_title=#{courseTitle},</if>" +
            "<if test='introduction!=null'>introduction=#{introduction},</if>" +
            "<if test='courseTitle!=null'>course_title=#{courseTitle},</if>" +
            "<if test='imageId!=null'>image_id=#{imageId},</if>" +
            "<if test='teacherId!=null'>teacher_id=#{teacherId},</if>" +
            "<if test='courseType!=null'>course_type=#{courseType},</if>" +
            "<if test='payType!=null'>pay_type=#{payType},</if>" +
            "<if test='type!=null'>type=#{type},</if>" +
            "<if test='createTime!=null'>create_time=#{createTime},</if>" +
            "<if test='updateTime!=null'>update_time=#{updateTime} </if>"+
            "where id=#{id}</script>")
    Integer updateCourseById(CourseInfo courseInfo);

    /**
     * @Author 杨友朋
     * @Description TODO 根据id修改课程状态
     * @Date 2023/6/30 9:38
     * @Return Integer
     * @param courseInfo
     */
    @Update("update course_info set course_status=#{courseStatus} where id=#{id}")
    Integer upStatusById(CourseInfo courseInfo);

    /**
    * @Author 杨友朋
    * @Description TODO 根据id删除课程
    * @Date 2023/6/30 9:38
    * @Return Integer
    * @param id
    */
    @Update("update course_info set del_flag=1 where id=#{id}")
    Integer delById(Integer id);

    /**
     * @Author 杨友朋
     * @Description TODO 根据ids批量删除课程
     * @Date 2023/6/21 20:24
     * @Return Integer
     * @param ids
     */
    @Update("<script>" +
            "update course_info set del_flag=1 where id IN " +
            " <foreach collection='ids' item='id' open='(' separator=',' close=')'>" +
            "            #{id}" +
            "        </foreach>" +
            "</script>")
    Integer delByIds(@Param(value = "ids") List<Integer> ids);

    /**
    * @Author 杨友朋
    * @Description TODO 批量上架
    * @Date 2023/6/30 10:49
    * @Return Integer
    * @param ids
    */
    @Update("<script>" +
            "update course_info set course_status='0' where id IN " +
            " <foreach collection='ids' item='id' open='(' separator=',' close=')'>" +
            "            #{id}" +
            "        </foreach>" +
            "</script>")
    Integer onShelf(@Param(value = "ids") List<Integer> ids);

    /**
    * @Author 杨友朋
    * @Description TODO 根据ids批量下架
    * @Date 2023/6/30 10:48
    * @Return Integer
    * @param ids
    */
    @Update("<script>" +
            "update course_info set course_status='1' where id IN " +
            " <foreach collection='ids' item='id' open='(' separator=',' close=')'>" +
            "            #{id}" +
            "        </foreach>" +
            "</script>")
    Integer offShelf(@Param(value = "ids") List<Integer> ids);

    @Select("SELECT t.id, t.t_name, COUNT(c.teacher_id) AS course_count " +
            "FROM teacher t LEFT JOIN course_info c ON t.id = c.teacher_id and c.del_flag=0 " +
            "WHERE t.del_flag = 0 GROUP BY t.id, t.t_name")
    List<CourseDTO> selectCountByTeacher();

    @Select("SELECT t.id, t.subject_type, COUNT(c.course_id) AS course_count " +
            "FROM `subject` t LEFT JOIN course_info c ON t.id = c.course_id and c.del_flag=0 " +
            "WHERE t.del_flag = 0 GROUP BY t.id, t.subject_type")
    List<CourseDTO> selectCountBySubject();
}
